#include "database.h"
#include "myutils.h"

#include <QDebug>
#include <QElapsedTimer>

DBPath m_db_path;

DataBase::DataBase()
{

}

static int callback(void *data, int argc, char **argv, char **azColName)
{
    int i;
    if (data == nullptr) {
        return 0;
    }

    for (i = 0; i < argc; i++) {
        snprintf((char *)data, 1024, "%s%s:%s;", (char *)data, azColName[i], argv[i] ? argv[i] : "NULL");
    }

    printf("\n");

    return 0;
}

static int callback_in_busy(void *ptr, int count)
{
    QThread::msleep(50);

    return 1;
}

static void SqlGetLock(sqlite3 *p_db, int ms)
{
    sqlite3_busy_handler(p_db, callback_in_busy, (void*)&ms);
}

// 辅助函数：安全提取文本字段（自动处理 NULL 值）
static std::string safe_column_text(sqlite3_stmt* stmt, int columnIndex)
{
    const char* text = reinterpret_cast<const char*>(sqlite3_column_text(stmt, columnIndex));
    return text ? text : "";
}

/* Create Device Code Database If Not Exist */
int DataBase::initDeviceCodeDB()
{
    int     ret = 0;
    string  sql = "";
    sqlite3 *db = nullptr;
    QString dbPath = "/etc/config/configTly.db";
    string _dbPath = dbPath.toStdString();

    if (!MyUtils::fileExists(dbPath))
    {
        ret = dbOpen(_dbPath, &db);
        if (ret == SQLITE_OK)
        {
            sql = "CREATE TABLE \"deviceInfo\" (\
                \"deviceCode\" TEXT PRIMARY KEY NOT NULL);";

            ret = dbExec(db, sql, NULL);
            if (ret == SQLITE_OK)
            {
                sql = "INSERT INTO 'deviceInfo' (deviceCode) VALUES ( 'T0000T');";

                ret = dbExec(db, sql, NULL);
                if (ret == SQLITE_OK)
                {
                    qInfo() << "[DB] Device Code DB Create Success";
                }
                else
                {
                    qCritical() << "[DB] Device Code DB Insert Default Device Id Failed" << ret;
                }
            }
            else
            {
                qCritical() << "[DB] Device Code DB Create Failed" << ret;
            }

            dbClose(db);
        }
        else
        {
            qCritical() << "[DB] Device Code DB Open Failed For Creating" << ret;
        }
    }

    return ret;
}

int DataBase::deviceCodeSelect(QString &deviceId)
{
    int             ret     = 0;
    string          sql     = "";
    sqlite3         *db     = NULL;
    sqlite3_stmt    *stmt   = NULL;
    const char      *zTail  = NULL;
    QString dbPath = "/etc/config/configTly.db";
    string _dbPath = dbPath.toStdString();

    if (MyUtils::fileExists(dbPath))
    {
        ret = dbOpen(_dbPath, &db);
        if (ret == SQLITE_OK)
        {
            sql = "SELECT * FROM deviceInfo;";

            ret = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, &zTail);
            switch (ret)
            {
            case SQLITE_OK:
                while (sqlite3_step(stmt) == SQLITE_ROW) {
                    string deviceCode  = (char*)sqlite3_column_text(stmt, 0);

                    deviceId = QString::fromStdString(deviceCode);
                }
                break;
            default:
                qWarning() << "[DB] Device Code DB Prepare Failed For Select" << ret;
                break;
            }

            sqlite3_finalize(stmt);

            dbClose(db);
        }
        else
        {
            qCritical() << "[DB] Device Code DB Open Failed For Select" << ret;
        }
    }
    else
    {
        qCritical() << "[DB] Device Code DB File Not Exist";
    }

    return ret;
}

int DataBase::drDataCreate()
{
    QElapsedTimer timer;
    timer.start();

    int     ret = 0;
    sqlite3 *db = nullptr;

    string _dbPath = m_db_path.GetDRPath();

    QString dbPath = QString::fromStdString(_dbPath);
    qDebug() << "[DB] DR Data DB File Path" << dbPath;

    if (MyUtils::fileExists(dbPath))
    {
        qCritical() << "[DB] DR Data DB File Already Exist" << dbPath;
        return 0;
    }

    ret = dbOpen(_dbPath, &db);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data DB Open Failed For Create" << ret;
        return ret;
    }

    const char *sql = "CREATE TABLE \"dr\" (\
                      \"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
                      \"dmDataCompanyId\" TEXT,\
                      \"roadType\" TEXT,\
                      \"dmDeviceCode\" TEXT,\
                      \"srcPictureDir\" TEXT NOT NULL,\
                      \"resPictureDir\" TEXT NOT NULL,\
                      \"pictureName\" TEXT NOT NULL,\
                      \"collectTime\" TEXT NOT NULL,\
                      \"longitude\" TEXT NOT NULL,\
                      \"latitude\" TEXT NOT NULL,\
                      \"speed\" TEXT NOT NULL,\
                      \"azimuth\" TEXT NOT NULL,\
                      \"res\" TEXT NOT NULL,\
                      \"algorithmTime\" TEXT NOT NULL,\
                      \"cameraType\" TEXT NOT NULL,\
                      \"autoApproval\" TEXT NOT NULL,\
                      \"gcjDamage\" TEXT NOT NULL,\
                      \"picResolution\" TEXT NOT NULL,\
                      \"sceneId\" TEXT NOT NULL,\
                      \"cameraAntennaAngle\" TEXT,\
                      \"cameraDistance\" TEXT,\
                      \"cameraVehicleAngle\" TEXT,\
                      \"gpsStatus\" TEXT,\
                      \"pitch\" TEXT);";

    ret = dbExec(db, sql, nullptr);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data DB Exec Failed For Create" << ret;
        dbClose(db);
        return ret;
    }

    dbClose(db);
    qInfo() << "[DB] DR Data DB Create Success Use" << timer.elapsed() << "ms";

    return ret;
}

int DataBase::drDataInsert(QQueue<DISEASE_INFO> &p_diseInfoQueue)
{
    QElapsedTimer timer;
    timer.start();

    int          ret    = 0;
    sqlite3      *db    = nullptr;
    sqlite3_stmt *stmt  = nullptr;

    string _dbPath = m_db_path.GetDRPath();

    QString dbPath = QString::fromStdString(_dbPath);
    if (!MyUtils::fileExists(dbPath))
    {
        qCritical() << "[DB] DR Data DB File Not Exist For Insert" << dbPath;
        drDataCreate();
        return -1;
    }


    ret = dbOpen(_dbPath, &db);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data DB Open Failed For Insert" << ret;
        return ret;
    }

    const char *sql = "INSERT INTO dr (dmDataCompanyId, roadType, dmDeviceCode, "
                      "srcPictureDir, resPictureDir, pictureName, collectTime, "
                      "longitude, latitude, speed, azimuth, res, algorithmTime, "
                      "cameraType, autoApproval, gcjDamage, picResolution, sceneId, "
                      "cameraAntennaAngle, cameraDistance, cameraVehicleAngle, "
                      "gpsStatus, pitch) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    ret = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data Insert Prepare Failed" << ret;
        sqlite3_finalize(stmt);
        dbClose(db);
        return ret;
    }

    ret = sqlite3_exec(db, "BEGIN IMMEDIATE TRANSACTION;", nullptr, nullptr, nullptr);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data Insert BEGIN IMMEDIATE TRANSACTION Failed" << ret;
        sqlite3_finalize(stmt);
        dbClose(db);
        return ret;
    }

    bool hasError = false;
    for (DISEASE_INFO &t_diseInfo : p_diseInfoQueue)
    {
        string dm_data_company_id   = t_diseInfo.dm_data_company_id.toStdString();
        string road_type            = t_diseInfo.road_type.toStdString();
        string device_id            = t_diseInfo.device_id.toStdString();
        string srcpic_dir           = t_diseInfo.srcpic_dir.toStdString();
        string respic_dir           = t_diseInfo.respic_dir.toStdString();
        string picture_name         = t_diseInfo.picture_name.toStdString();
        string collect_time         = t_diseInfo.collect_time.toStdString();
        string longitude            = t_diseInfo.longitude.toStdString();
        string latitude             = t_diseInfo.latitude.toStdString();
        string speed                = t_diseInfo.speed.toStdString();
        string azimuth              = t_diseInfo.azimuth.toStdString();
        string res                  = t_diseInfo.res.toStdString();
        string algorithm_time       = t_diseInfo.algorithm_time.toStdString();
        string camera_type          = t_diseInfo.camera_type.toStdString();
        string autoApproval         = t_diseInfo.autoApproval.toStdString();
        string gcjDamage            = t_diseInfo.gcjDamage.toStdString();
        string picResolution        = t_diseInfo.picResolution.toStdString();
        string sceneId              = t_diseInfo.sceneId.toStdString();
        string cameraAntennaAngle   = t_diseInfo.cameraAntennaAngle.toStdString();
        string cameraDistance       = t_diseInfo.cameraDistance.toStdString();
        string cameraVehicleAngle   = t_diseInfo.cameraVehicleAngle.toStdString();
        string gpsStatus            = t_diseInfo.gpsStatus.toStdString();
        string pitch                = t_diseInfo.pitch.toStdString();

        sqlite3_bind_text(stmt, 1,  dm_data_company_id.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 2,  road_type.c_str(),          -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 3,  device_id.c_str(),          -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 4,  srcpic_dir.c_str(),         -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 5,  respic_dir.c_str(),         -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 6,  picture_name.c_str(),       -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 7,  collect_time.c_str(),       -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 8,  longitude.c_str(),          -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 9,  latitude.c_str(),           -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 10, speed.c_str(),              -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 11, azimuth.c_str(),            -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 12, res.c_str(),                -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 13, algorithm_time.c_str(),     -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 14, camera_type.c_str(),        -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 15, autoApproval.c_str(),       -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 16, gcjDamage.c_str(),          -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 17, picResolution.c_str(),      -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 18, sceneId.c_str(),            -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 19, cameraAntennaAngle.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 20, cameraDistance.c_str(),     -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 21, cameraVehicleAngle.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 22, gpsStatus.c_str(),          -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 23, pitch.c_str(),              -1, SQLITE_STATIC);

        // 执行插入并检查结果
        ret = sqlite3_step(stmt);
        if (ret != SQLITE_DONE) {
            qCritical() << "[DB] DR Data Insert Step Failed" << sqlite3_errmsg(db);
            hasError = true;
            break;
        }
        sqlite3_reset(stmt);
    }

    if (hasError)
    {
        sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
        ret = SQLITE_ERROR;
    }
    else
    {
        ret = sqlite3_exec(db, "COMMIT;", nullptr, nullptr, nullptr);
        if (ret != SQLITE_OK)
        {
            qCritical() << "[DB] DR Data Insert COMMIT Failed" << ret;
        }
    }

    sqlite3_finalize(stmt);
    dbClose(db);

    qInfo() << "[DB] Insert DR Data Use" << timer.elapsed() << "ms";

    return ret;
}

int DataBase::drDataSelect(int p_id, QQueue<DISEASE_INFO> &p_diseInfoQueue)
{
    QElapsedTimer timer;
    timer.start();

    int          ret    = 0;
    sqlite3      *db    = nullptr;
    sqlite3_stmt *stmt  = nullptr;

    string _dbPath = m_db_path.GetDRPath();

    QString dbPath = QString::fromStdString(_dbPath);
    if (!MyUtils::fileExists(dbPath))
    {
        qCritical() << "[DB] DR Data DB File Not Exist For Select" << dbPath;
        drDataCreate();
        return -1;
    }

    ret = dbOpen(_dbPath, &db);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data DB Open Failed For Select" << ret;
        return ret;
    }

    const char *sql = "SELECT * FROM dr WHERE id > ? ORDER BY id LIMIT 100;";

    ret = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data Select Prepare Failed" << ret;
        sqlite3_finalize(stmt);
        dbClose(db);
        return ret;
    }

    // 绑定参数（安全且高效）
    sqlite3_bind_int(stmt, 1, p_id);

    int rowCount = 0;
    // 执行查询...
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 使用辅助函数提取字段（自动处理 NULL）
        DISEASE_INFO t_diseInfo;
        t_diseInfo.sql_id               = QString::number(sqlite3_column_int(stmt, ColumnIndex::ID));
        t_diseInfo.dm_data_company_id   = QString::fromStdString(safe_column_text(stmt, ColumnIndex::DM_DATA_COMPANY_ID));
        t_diseInfo.road_type            = QString::fromStdString(safe_column_text(stmt, ColumnIndex::ROAD_TYPE));
        t_diseInfo.device_id            = QString::fromStdString(safe_column_text(stmt, ColumnIndex::DEVICE_ID));
        t_diseInfo.srcpic_dir           = QString::fromStdString(safe_column_text(stmt, ColumnIndex::SRC_PIC_DIR));
        t_diseInfo.respic_dir           = QString::fromStdString(safe_column_text(stmt, ColumnIndex::RES_PIC_DIR));
        t_diseInfo.picture_name         = QString::fromStdString(safe_column_text(stmt, ColumnIndex::PICTURE_NAME));
        t_diseInfo.collect_time         = QString::fromStdString(safe_column_text(stmt, ColumnIndex::COLLECT_TIME));
        t_diseInfo.longitude            = QString::fromStdString(safe_column_text(stmt, ColumnIndex::LONGITUDE));
        t_diseInfo.latitude             = QString::fromStdString(safe_column_text(stmt, ColumnIndex::LATITUDE));
        t_diseInfo.speed                = QString::fromStdString(safe_column_text(stmt, ColumnIndex::SPEED));
        t_diseInfo.azimuth              = QString::fromStdString(safe_column_text(stmt, ColumnIndex::AZIMUTH));
        t_diseInfo.res                  = QString::fromStdString(safe_column_text(stmt, ColumnIndex::RES));
        t_diseInfo.algorithm_time       = QString::fromStdString(safe_column_text(stmt, ColumnIndex::ALGORITHM_TIME));
        t_diseInfo.camera_type          = QString::fromStdString(safe_column_text(stmt, ColumnIndex::CAMERA_TYPE));
        t_diseInfo.autoApproval         = QString::fromStdString(safe_column_text(stmt, ColumnIndex::AUTO_APPROVAL));
        t_diseInfo.gcjDamage            = QString::fromStdString(safe_column_text(stmt, ColumnIndex::GCJ_DAMAGE));
        t_diseInfo.picResolution        = QString::fromStdString(safe_column_text(stmt, ColumnIndex::PIC_RESOLUTION));
        t_diseInfo.sceneId              = QString::fromStdString(safe_column_text(stmt, ColumnIndex::SCENE_ID));
        t_diseInfo.cameraAntennaAngle   = QString::fromStdString(safe_column_text(stmt, ColumnIndex::CAMERA_ANTENNA_ANGLE));
        t_diseInfo.cameraDistance       = QString::fromStdString(safe_column_text(stmt, ColumnIndex::CAMERA_DISTANCE));
        t_diseInfo.cameraVehicleAngle   = QString::fromStdString(safe_column_text(stmt, ColumnIndex::CAMERA_VEHICLE_ANGLE));
        t_diseInfo.gpsStatus            = QString::fromStdString(safe_column_text(stmt, ColumnIndex::GPS_STATUS));
        t_diseInfo.pitch                = QString::fromStdString(safe_column_text(stmt, ColumnIndex::PITCH));

        p_diseInfoQueue.enqueue(t_diseInfo);
        rowCount++;
    }

    sqlite3_finalize(stmt);
    dbClose(db);

    qInfo() << "[DB] Select DR Data" << rowCount << "Rows Use" << timer.elapsed() << "ms";

    return ret;
}

int DataBase::drDataOtherSelect(int &all, int &valid, int &upload, int maxUploadId)
{
    QElapsedTimer timer;
    timer.start();

    int          ret    = 0;
    sqlite3      *db    = nullptr;
    sqlite3_stmt *stmt  = nullptr;

    string _dbPath = m_db_path.GetDRPath();

    QString dbPath = QString::fromStdString(_dbPath);
    if (!MyUtils::fileExists(dbPath))
    {
        qCritical() << "[DB] DR Data DB File Not Exist For Other Select" << dbPath;
        return -1;
    }

    ret = dbOpen(_dbPath, &db);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data DB Open Failed For Other Select" << ret;
        return ret;
    }

    // 查询语句
    const char *sql = R"(
        SELECT
            COUNT(*) AS Total,
            SUM(CASE WHEN dmDataCompanyId != 'no_id' THEN 1 ELSE 0 END) AS Valid,
            SUM(CASE WHEN dmDataCompanyId != 'no_id' AND id <= ? THEN 1 ELSE 0 END) AS Upload
        FROM dr;
    )";

    ret = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);
    if (ret != SQLITE_OK)
    {
        qCritical() << "[DB] DR Data Other Select Prepare Failed" << ret;
        sqlite3_finalize(stmt);
        dbClose(db);
        return ret;
    }

    // 绑定参数（maxUploadId）
    sqlite3_bind_int(stmt, 1, maxUploadId);

    // 执行查询...
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        all = sqlite3_column_int(stmt, 0);    // 第一列: Total
        valid = sqlite3_column_int(stmt, 1);  // 第二列: Valid
        upload = sqlite3_column_int(stmt, 2); // 第三列: Upload
    }

    sqlite3_finalize(stmt);
    dbClose(db);

    qInfo() << "[DB] Other Select DR Data Use" << timer.elapsed() << "ms";

    return ret;
}

int DataBase::dbOpen(const string dbpath, sqlite3 **db)
{
    int         ret;

    ret = sqlite3_open_v2(dbpath.c_str(), db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, NULL);
    if (ret != SQLITE_OK) {
        qCritical() << QString().sprintf("[DB] %s Open Failed %s", dbpath.c_str(), sqlite3_errmsg(*db));
        return ret;
    }

    sqlite3_busy_timeout(*db, 20);

    // 启用 WAL 模式
    ret = sqlite3_exec(*db, "PRAGMA journal_mode=WAL;", nullptr, nullptr, nullptr);
    if (ret != SQLITE_OK) {
        qCritical() << "[DB] Enable WAL Failed" << sqlite3_errmsg(*db);
        sqlite3_close(*db);
        return ret;
    }

    // 配置自动检查点（每 1000 页触发一次，可根据需求调整）
    ret = sqlite3_exec(*db, "PRAGMA wal_autocheckpoint=10;", nullptr, nullptr, nullptr);
    if (ret != SQLITE_OK) {
        qCritical() << "[DB] Set Auto-Checkpoint Failed" << sqlite3_errmsg(*db);
        // 不终止，继续运行
    }

    return ret;
}

int DataBase::dbExec(sqlite3 *p_db, const string str_sql, char *p_data)
{
    int  ret = -1;
    char *zErrMsg = nullptr;

    if (!p_db) {
        return ret;
    }

    ret = sqlite3_exec(p_db, str_sql.c_str(), callback, p_data, &zErrMsg);
    if (ret != SQLITE_OK) {
        sqlite3_free(zErrMsg);
    }

    return ret;
}

int DataBase::dbClose(sqlite3 *p_db)
{
    int ret = sqlite3_close_v2(p_db);
    if (ret != SQLITE_OK) {
        qCritical() << "[DB] Sqlite3 Close V2 Failed" << ret;
    }

    return ret;
}
